package com.powerunion.datacollection.dao.impl;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import net.sf.json.JSONObject;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import com.powerunion.datacollection.common.Constants;
import com.powerunion.datacollection.dao.DaoException;
import com.powerunion.datacollection.dao.FormFieldDao;
import com.powerunion.datacollection.model.FieldItem;
import com.powerunion.datacollection.model.FieldItems;
import com.powerunion.datacollection.model.FormField;
import com.powerunion.datacollection.model.SysServer;
import com.powerunion.datacollection.persistence.db.jdbc.operator.QueryOperator;
import com.powerunion.datacollection.persistence.db.jdbc.operator.RsDataSet;
import com.powerunion.datacollection.persistence.db.jdbc.operator.SqlParameter;
import com.powerunion.datacollection.persistence.db.jdbc.operator.SqlParameterSet;
import com.powerunion.datacollection.persistence.db.jdbc.operator.SqlType;
import com.powerunion.datacollection.persistence.db.jdbc.operator.UpdateOperator;
import com.powerunion.util.DBUtils;
import com.powerunion.util.log.LogHelper;

public class FormFieldDaoImpl extends JdbcBase implements FormFieldDao {
	// 初始化日志类
	private static final Logger logger = LogHelper.getInstance().getLogger(
			FormFieldDaoImpl.class);
	
	private String[] findFormFieldIdsByFormId(String formId,Connection conn) 
		throws DaoException{
		String sql = "select id from dc_maintbl_form where form_id=?";
		QueryOperator query = new QueryOperator();
		query.setSql(sql);
		query.addParameter(new SqlParameter(SqlType.VARCHAR,formId));
		query.setUseOnlyConnectionFlag(true);
		query.setPresentConnection(conn);
		String[] formFieldIds = null;
		try{
			query.access();
			if(query.resultSetAvailable()){
				
				//获取并处理记录集
				RsDataSet rs = new RsDataSet(query.getSqlResultSet());
				formFieldIds = new String[rs.rowCount];
				for(int i=0;i<rs.rowCount;i++){
					String id = rs.getFieldValueAsString("id");
					formFieldIds[i] = id;
					rs.next();
				}

				rs.clearAll();
			}
		}catch(Exception e){
			logger.error("", e);
			throw new DaoException("查找表单ID为："+formId+" 的字段id出错！");
		}
		return formFieldIds;
	}
	
	public boolean delete(String formId) throws DaoException{

		if (logger.isDebugEnabled()) {
			logger.debug(Constants.INTOMETHOD + "delete");
		}
		Connection conn = null;
		String sql = "delete from dc_maintbl_form where form_id = ? ";
		UpdateOperator update = new UpdateOperator(); 
		update.setSql(sql);
		update.setUseOnlyConnectionFlag(true);
		try{
			conn = this.getConnection();
			String[] formFieldIds = this.findFormFieldIdsByFormId(formId, conn);
			//先删除下拉框或Radio类型对应的内容值
			this.deleteFieldItemByFormId(formFieldIds);
			
			update.setPresentConnection(conn);
			update.addParameter(new SqlParameter(SqlType.VARCHAR,formId));
			update.access();
		}catch(Exception e){
			logger.error("", e);
			throw new DaoException("删除表单ID为："+formId+" 的字段定义信息出错！");
		}finally{
			DBUtils.close(conn);
		}
		
		int effectCount = update.getEffectCounts();
		if(effectCount >0) {
			if (logger.isDebugEnabled()) {
				logger.debug("delete:" + Constants.DELETE + "表单ID为："+formId+" 的字段定义信息"
						+ Constants.SUCCESS);
			}
			return true;
		}else{
			if (logger.isDebugEnabled()) {
				logger.debug("delete:" + Constants.DELETE + "表单ID为："+formId+" 的字段定义信息"
						+ Constants.FAIL);
			}
			return false;
		}
	
	}
	
	private boolean deleteFieldItemByFormId(String[] codes) throws DaoException {
		logger.debug(Constants.INTOMETHOD + "deleteFieldItemByFormId");
		String sql = "delete from dc_field_item where form_field_id=?";
		boolean isSuccess = false;
		List batchParams = this.batchParams(codes, Types.VARCHAR);
		if (batchParams != null) {
			logger.error("delete:" + Constants.DELETE + "下拉框类型字段对应的内容值"
					+ Constants.SUCCESS);
			try {
				this.executeBatch(sql, batchParams);
				isSuccess = true;
			} catch (SQLException e) {
				logger.error("",e);
				throw new DaoException("删除下拉框类型字段对应的内容值出错");
			}
			
			return isSuccess;
		} else {
			return isSuccess;
		}
	}


	public FormField getBykey(String id) {
		FormField formField = new FormField();
		StringBuffer sql = new StringBuffer();
		sql.append("select id,field_label,field_name,field_input,field_type ,")
		   .append("table_field,table_flag,form_id,ordr_no from dc_form_field  where id = ? ");
		QueryOperator query = new QueryOperator();
		query.setSql(sql.toString());
		query.addParameter(new SqlParameter(SqlType.VARCHAR,id));
		query.access();
		if(query.resultSetAvailable()) {//检查结果集是否可用
			//获取并处理记录集
			RsDataSet rs = new RsDataSet(query.getSqlResultSet());
			if(rs.rowCount > 0) {
				formField.setId(rs.getFieldValueAsString("id"));
				formField.setFieldInput(rs.getFieldValueAsString("field_input"));
				
			}
			rs.clearAll();
		}
		return formField;
	
	}

	public List getFormFields(String formId) throws DaoException{
		
		StringBuffer sqlStr = new StringBuffer();
		sqlStr
				.append(
						"select id,field_label,field_name,field_input,field_type,date_type_format,")
			    .append("form_id,form_id,ordr_no from dc_maintbl_form where form_id = ?  order by ordr_no");
		Connection conn = null;
    	QueryOperator query = new QueryOperator();
    	List formFields = null;
    	try{
    		conn = this.getConnection();
        	query.setSql(sqlStr.toString());
    		query.addParameter(new SqlParameter(SqlType.VARCHAR,formId));
    		query.setUseOnlyConnectionFlag(true);
    		query.setPresentConnection(conn);
    		
    		query.access();
    		formFields =  new ArrayList();
    		if(query.resultSetAvailable()){
				//获取并处理记录集
				RsDataSet rs = new RsDataSet(query.getSqlResultSet());
				for(int i=0;i<rs.rowCount;i++){					
					FormField formField = new FormField();
					formField.setFieldInput(rs.getFieldValueAsString("field_input"));
					formField.setFieldInputStr(rs.getFieldValueAsString("field_input"));
					formField.setTableField(rs.getFieldValueAsString("field_name"));
					formField.setFieldLabel(rs.getFieldValueAsString("field_label"));
					formField.setTfieldName(rs.getFieldValueAsString("field_name"));
					formField.setTfieldType(rs.getFieldValueAsString("field_type"));
					formField.setId(rs.getFieldValueAsString("id"));
					formField.setFormId(rs.getFieldValueAsString("form_id"));
					formField.setDisplayOrder(rs.getFieldValueAsInteger("ordr_no").intValue());
					formField.setDateFormat(rs.getFieldValueAsString("date_type_format"));
					
					FieldItems fieldItems = this.getFieldItems(formField.getId(), conn);
					JSONObject jsonObject = JSONObject.fromObject(fieldItems);
					formField.setFieldItems(jsonObject.toString());
					
					formFields.add(formField);
					rs.next();
				}
				rs.clearAll();
			}
    		
    	}catch(Exception e){
    		logger.error("", e);
    		throw new DaoException("getFormFields throw of error!");
    	}finally{
    		DBUtils.close(conn);
    	}

		return formFields;
	}
	
	public boolean save(List formFields,String formId) throws DaoException{
		logger.debug(Constants.INTOMETHOD + "FormFields");
		StringBuffer sql = new StringBuffer();
		sql.append("insert into dc_maintbl_form(id,field_label,field_name,field_input,field_type,")
		   .append("date_type_format,form_id,ordr_no) values(?,?,?,?,?,?,?,?)");
		
		Connection conn = null;
		boolean flag = false;
		try{
			conn = this.getConnection();
		
			for(Iterator iter = formFields.iterator();iter.hasNext();) {
				UpdateOperator update = new UpdateOperator(); 
				update.setSql(sql.toString());
				update.setUseOnlyConnectionFlag(true);
				update.setPresentConnection(conn);
				
				FormField formField = (FormField)iter.next();

				String id = String.valueOf(this.getSeq("dc_maintbl_form"));
				update.addParameter(new SqlParameter(SqlType.VARCHAR,id));
				update.addParameter(new SqlParameter(SqlType.VARCHAR,formField.getFieldLabel()));
				update.addParameter(new SqlParameter(SqlType.VARCHAR,formField.getTfieldName()));
				update.addParameter(new SqlParameter(SqlType.VARCHAR,formField.getFieldInput()));
				update.addParameter(new SqlParameter(SqlType.VARCHAR,formField.getTfieldType()));
				update.addParameter(new SqlParameter(SqlType.VARCHAR,formField.getDateFormat()));
				update.addParameter(new SqlParameter(SqlType.VARCHAR,formId));
				update.addParameter(new SqlParameter(SqlType.INTEGER,new Integer(formField.getDisplayOrder())));
				
				update.access();
				int effectCount = update.getEffectCounts();
				if(effectCount > 0){
					if(!StringUtils.isEmpty(formField.getFieldItems()) && !formField.getFieldItems().equals("null")) {
						String fieldItemsStr = formField.getFieldItems();
						Map classMap = new HashMap();  
						classMap.put("items", FieldItem.class);
						FieldItems fi = (FieldItems)JSONObject.toBean(JSONObject.fromObject(fieldItemsStr), FieldItems.class,classMap);
						this.saveFieldItems(id, fi.getContentType(), fi.getItems(), conn);
					}
				}
			}
			flag = true;
		}catch(Exception e){
			logger.error("", e);
			throw new DaoException("保存表单ID为："+formId+" 的字段定义信息出错！");
		}finally{
			DBUtils.close(conn);
		}
	
		if(flag) {
			if (logger.isDebugEnabled()) {
				logger.debug("save:" + Constants.SAVE + "表单ID为："+formId+" 的字段定义信息"
						+ Constants.SUCCESS);
			}
			return true;
		}else{
			if (logger.isDebugEnabled()) {
				logger.debug("save:" + Constants.SAVE + "表单ID为："+formId+" 的字段定义信息"
						+ Constants.FAIL);
			}
			return false;
		}

	}
	
    public List getServers() throws DaoException {
    	String sql = "select svr_name from sys_svr";
    	QueryOperator query = new QueryOperator();
    	query.setSql(sql);
    	List servers = null;
    	try{
    		servers = new ArrayList();
			query.access();
			if(query.resultSetAvailable()){
				
				//获取并处理记录集
				RsDataSet rs = new RsDataSet(query.getSqlResultSet());
				for(int i=0;i<rs.rowCount;i++){
					String svrName = rs.getFieldValueAsString("svr_name");
					SysServer server = new SysServer();
					server.setSvrName(svrName);
					servers.add(server);
					rs.next();
				}

				rs.clearAll();
			}
		}catch(Exception e){
			logger.error("", e);
			throw new DaoException("查找服务器出错！");
		}
		return servers;
    }
	
    private boolean saveFieldItems(String formFieldId,int contentType,List items,Connection conn) throws DaoException {
    	StringBuffer sql = new StringBuffer();
    	sql.append("insert into dc_field_item(id,item_label,item_value,ordr_no,")
    	   .append("form_field_id,item_type,SVR_NAME,tbl_name) values(?,?,?,?,?,?,?,?)");
    	UpdateOperator update = new UpdateOperator();
    	update.setSql(sql.toString());
    	update.setUseOnlyConnectionFlag(true);
    	update.setPresentConnection(conn);
    	try{
    		for(Iterator iter = items.iterator();iter.hasNext();) {
    			FieldItem item = (FieldItem)iter.next();
    			SqlParameterSet spSet = new SqlParameterSet();
    			String id = String.valueOf(this.getSeq("dc_field_item"));
    			spSet.addParameter(new SqlParameter(SqlType.VARCHAR,id));
    			spSet.addParameter(new SqlParameter(SqlType.VARCHAR,item.getItemLabel()));
    			spSet.addParameter(new SqlParameter(SqlType.VARCHAR,item.getItemValue()));
    			spSet.addParameter(new SqlParameter(SqlType.INTEGER,new Integer(item.getDisplayOrder())));
    			spSet.addParameter(new SqlParameter(SqlType.VARCHAR,formFieldId));
    			spSet.addParameter(new SqlParameter(SqlType.INTEGER,new Integer(contentType)));
    			spSet.addParameter(new SqlParameter(SqlType.VARCHAR,item.getSvrName()));
    			spSet.addParameter(new SqlParameter(SqlType.VARCHAR,item.getTblName()));
    			
    			update.addBatchParameter(spSet);
    		}
    		update.access();
    	}catch(Exception e){
    		logger.error("", e);
    		throw new DaoException("保存ID为："+formFieldId+" 的字段的下拉框的列表元素出错！");
    	}
    	
		int[] effectCount = update.getBatchEffectCounts();
		if(effectCount != null && effectCount.length>0) {
			if (logger.isDebugEnabled()) {
				logger.debug("save:" + Constants.SAVE + "ID为："+formFieldId+" 的字段的下拉框的列表元素"
						+ Constants.SUCCESS);
			}
			return true;
		}else{
			if (logger.isDebugEnabled()) {
				logger.debug("save:" + Constants.SAVE + "ID为："+formFieldId+" 的字段的下拉框的列表元素"
						+ Constants.FAIL);
			}
			return false;
		}
    }

    private FieldItems getFieldItems(String formFieldId,Connection conn) throws DaoException {
    	StringBuffer sql = new StringBuffer();
    	sql.append("select item_label,item_value,ordr_no,item_type,svr_name,tbl_name ")
    	   .append(" from dc_field_item where form_field_id = ? order by ordr_no ");
    	QueryOperator query = new QueryOperator();
    	query.setSql(sql.toString());
		query.addParameter(new SqlParameter(SqlType.VARCHAR,formFieldId));
		query.setUseOnlyConnectionFlag(true);
		query.setPresentConnection(conn);
		FieldItems fieldItems = null;
		List items = null;
    	try{
    		query.access();
			if(query.resultSetAvailable()){
				fieldItems = new FieldItems();
				items = new ArrayList();
				//获取并处理记录集
				RsDataSet rs = new RsDataSet(query.getSqlResultSet());
				for(int i=0;i<rs.rowCount;i++){					
					FieldItem fi = new FieldItem();
					fi.setItemLabel( rs.getFieldValueAsString("item_label"));
					fi.setItemValue(rs.getFieldValueAsString("item_value"));
					fi.setDisplayOrder(Integer.parseInt(rs.getFieldValueAsString("ordr_no")));
					int itemType = rs.getFieldValueAsInteger("item_type").intValue();
					fieldItems.setContentType(itemType);
					fi.setSvrName(rs.getFieldValueAsString("svr_name"));
					fi.setTblName(rs.getFieldValueAsString("tbl_name"));
					items.add(fi);
					
					rs.next();
				}
				
				fieldItems.setItems(items);
				
				rs.clearAll();
			}
    	}catch(Exception e){
    		logger.error("", e);
    		throw new DaoException("getFieldItems throw of error!");
    	}
    	return fieldItems;
    	
    }
    
   
}
